github.com/turbot/steampipe@v1.7.0-rc.0.0.20240517123944-7cef272d4458/tests/manual_testing/many controls/queries/q13.sql (about)

     1  with data as (
     2      select
     3          distinct arn
     4      from
     5          aws_cloudfront_distribution,
     6          jsonb_array_elements(
     7                  case jsonb_typeof(cache_behaviors -> 'Items')
     8                      when 'array' then (cache_behaviors -> 'Items')
     9                      else null end
    10              ) as cb
    11      where
    12                  cb -> 'ViewerProtocolPolicy' = '"allow-all"'
    13  )
    14  select
    15      -- Required Columns
    16      b.arn as resource,
    17      case
    18          when d.arn is not null or (default_cache_behavior ->> 'ViewerProtocolPolicy' = 'allow-all') then 'alarm'
    19          else 'ok'
    20          end as status,
    21      case
    22          when d.arn is not null or (default_cache_behavior ->> 'ViewerProtocolPolicy' = 'allow-all') then title || ' data not encrypted in transit.'
    23          else title || ' data encrypted in transit.'
    24          end as reason,
    25      -- Additional Dimensions
    26      region,
    27      account_id
    28  from
    29      aws_cloudfront_distribution as b
    30          left join data as d on b.arn = d.arn;
    31